/*
 * ReportFrame.java
 */

package org.owasp.scrubbr.db.report;

import org.owasp.scrubbr.db.Table;
import org.owasp.scrubbr.db.TaintedRow;
import org.owasp.scrubbr.db.Tuple;
import org.owasp.scrubbr.util.DatabaseUtil;
import org.owasp.scrubbr.util.GeneralUtil;
import java.awt.Color;
import java.io.PrintWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import javax.swing.JSplitPane;
import javax.swing.table.TableCellRenderer;
import javax.swing.JFrame;

public class ReportFrame extends JFrame {

    private Report rpt = null;
    private ColumnSelectionDialog dlgColumns;
    private String startDBDelimiter;
    private String endDBDelimiter;

    public ReportFrame(Report rpt) {

        initComponents();

        switch (DatabaseUtil.DATABASE_TYPE) {
            case DatabaseUtil.ORACLE:
                startDBDelimiter = DatabaseUtil.DBORACLE.getEncapsulateStart();
                endDBDelimiter = DatabaseUtil.DBORACLE.getEncapsulateEnd();
                break;
            case DatabaseUtil.MYSQL:
                startDBDelimiter = DatabaseUtil.DBMYSQL.getEncapsulateStart();
                endDBDelimiter = DatabaseUtil.DBMYSQL.getEncapsulateEnd();
                break;
            case DatabaseUtil.MSSQL:
                startDBDelimiter = DatabaseUtil.DBMSSQL.getEncapsulateStart();
                endDBDelimiter = DatabaseUtil.DBMSSQL.getEncapsulateEnd();
                break;
            default:
                throw new UnsupportedOperationException("Bad DB type received in ReportFrame");
        }

        this.rpt = rpt;
        this.dlgColumns = new ColumnSelectionDialog(tblRows);

        List<Table> tables = rpt.getScannedTables();

        Iterator it = tables.iterator();

        while (it.hasNext()) {
            Table t = (Table) it.next();
            lstTable.add(t.getName());
        }

        int hours = 0;
        int minutes = 0;
        int seconds = 0;

        double remaining = rpt.getScanTime();

        hours = (int) remaining / (60 * 60);
        remaining -= (hours * 60 * 60);

        minutes = (int) remaining / (60);
        remaining -= (minutes * 60);

        seconds = (int) remaining;

        DecimalFormat df = new DecimalFormat("###00");

        lblScanTime.setText(String.format("%s:%s:%s",
                new Object[]{
                    df.format(hours),
                    df.format(minutes),
                    df.format(seconds)}));

        lblRowsScanned.setText(String.valueOf(rpt.getRowsScanned()));
        lblTablesScanned.setText(String.valueOf(rpt.getNumberOfTablesScanned()));
        lblTaintedRows.setText(String.valueOf(rpt.getTaintedRows()));
        lblTaintedTables.setText(String.valueOf(rpt.getScannedTables().size()));
        lblTaintedTuples.setText(String.valueOf(rpt.getTaintedTuples()));

    }

    public void loadRows(Table t) {

        DBTableModel db = new DBTableModel(t);

        dlgColumns.setTable(t);

        //use XTableColumnModel to allow column hiding

        XTableColumnModel xTableColumnModel = new XTableColumnModel();
        tblRows.setColumnModel(xTableColumnModel);
        tblRows.setModel(db);
        tblRows.createDefaultColumnsFromModel();

        tblRows.addMouseListener(new MouseClickListener(tblRows, btnExplain));

        TableCellRenderer renderer = new ColorizedTableCellRenderer();

        tblRows.setDefaultRenderer(String.class, renderer);

        CellSelectionListener csl = new CellSelectionListener(tblRows, txtOriginalValue, txtCleanedValue, pnlIssueDetails, btnFix, btnMarkClean, btnExplain);

        tblRows.getColumnModel().getSelectionModel().addListSelectionListener(csl);
        tblRows.getSelectionModel().addListSelectionListener(csl);

        double preferredHeight = pnRowsHolder.getPreferredSize().getHeight();

        pnlTableRows.setDividerLocation(Math.min(.4, preferredHeight / pnlTableRows.getPreferredSize().getHeight()));

    }

    private String dbObjectWrap(String s) {
        return startDBDelimiter + s + endDBDelimiter;
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        pnlTables = new javax.swing.JPanel();
        lbLabellTablesScanned = new javax.swing.JLabel();
        lblLabelTaintedTables = new javax.swing.JLabel();
        lblLabelRowsScanned = new javax.swing.JLabel();
        lblLabelTaintedRows = new javax.swing.JLabel();
        lblLabelTaintedTuples = new javax.swing.JLabel();
        lblTablesScanned = new javax.swing.JLabel();
        lblRowsScanned = new javax.swing.JLabel();
        lblTaintedRows = new javax.swing.JLabel();
        lblTaintedTuples = new javax.swing.JLabel();
        lstTable = new java.awt.List();
        lblLabelNumTablesTainted = new javax.swing.JLabel();
        lblTaintedTables = new javax.swing.JLabel();
        lblLabelScanTime = new javax.swing.JLabel();
        lblScanTime = new javax.swing.JLabel();
        pnlRows = new javax.swing.JPanel();
        pnlTableRows = new JSplitPane(JSplitPane.VERTICAL_SPLIT,tblRows,pnlIssueDetails);
        pnRowsHolder = new javax.swing.JScrollPane();
        tblRows = new javax.swing.JTable();
        pnlIssueDetails = new javax.swing.JPanel();
        lblOriginalTupleValue = new javax.swing.JLabel();
        lblCleanedTupleValue = new javax.swing.JLabel();
        scrollOriginalValue = new javax.swing.JScrollPane();
        txtOriginalValue = new javax.swing.JTextArea();
        scrollCleanedValue = new javax.swing.JScrollPane();
        txtCleanedValue = new javax.swing.JTextArea();
        btnFix = new javax.swing.JButton();
        btnSkip = new javax.swing.JButton();
        btnMarkClean = new javax.swing.JButton();
        btnExplain = new javax.swing.JButton();
        btnGenerateReport = new javax.swing.JButton();
        btnManageColumns = new javax.swing.JButton();
        lblStatus = new javax.swing.JLabel();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        setTitle("Scrubbr - Scan Results");
        addWindowListener(new java.awt.event.WindowAdapter() {
            public void windowClosing(java.awt.event.WindowEvent evt) {
                formWindowClosing(evt);
            }
        });

        pnlTables.setBorder(javax.swing.BorderFactory.createTitledBorder("Table Breakdown"));

        lbLabellTablesScanned.setText("Tables Scanned:");

        lblLabelTaintedTables.setText("Tainted Tables (select to see details):");

        lblLabelRowsScanned.setText("Rows Scanned:");

        lblLabelTaintedRows.setText("Tainted Rows Found:");

        lblLabelTaintedTuples.setText("Tainted Tuples (values):");

        lblTablesScanned.setText("0");

        lblRowsScanned.setText("0");

        lblTaintedRows.setFont(new java.awt.Font("Tahoma", 1, 11));
        lblTaintedRows.setForeground(java.awt.Color.red);
        lblTaintedRows.setText("0");

        lblTaintedTuples.setFont(new java.awt.Font("Tahoma", 1, 14));
        lblTaintedTuples.setForeground(java.awt.Color.red);
        lblTaintedTuples.setText("0");

        lstTable.setName(""); // NOI18N
        lstTable.addItemListener(new java.awt.event.ItemListener() {
            public void itemStateChanged(java.awt.event.ItemEvent evt) {
                lstTableItemStateChanged(evt);
            }
        });

        lblLabelNumTablesTainted.setText("Tainted Tables:");

        lblTaintedTables.setText("0");

        lblLabelScanTime.setText("Scan Time:");

        lblScanTime.setText("0:00");

        javax.swing.GroupLayout pnlTablesLayout = new javax.swing.GroupLayout(pnlTables);
        pnlTables.setLayout(pnlTablesLayout);
        pnlTablesLayout.setHorizontalGroup(
            pnlTablesLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(pnlTablesLayout.createSequentialGroup()
                .addContainerGap()
                .addGroup(pnlTablesLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(pnlTablesLayout.createSequentialGroup()
                        .addComponent(lblLabelTaintedRows, javax.swing.GroupLayout.PREFERRED_SIZE, 121, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(233, 233, 233))
                    .addGroup(pnlTablesLayout.createSequentialGroup()
                        .addComponent(lblLabelNumTablesTainted)
                        .addContainerGap(215, Short.MAX_VALUE))
                    .addGroup(pnlTablesLayout.createSequentialGroup()
                        .addComponent(lblLabelTaintedTables, javax.swing.GroupLayout.PREFERRED_SIZE, 244, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addContainerGap(45, Short.MAX_VALUE))
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, pnlTablesLayout.createSequentialGroup()
                        .addGroup(pnlTablesLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(pnlTablesLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                .addGroup(pnlTablesLayout.createSequentialGroup()
                                    .addGroup(pnlTablesLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                        .addComponent(lbLabellTablesScanned, javax.swing.GroupLayout.PREFERRED_SIZE, 101, javax.swing.GroupLayout.PREFERRED_SIZE)
                                        .addComponent(lblLabelTaintedTuples))
                                    .addGap(31, 31, 31))
                                .addGroup(pnlTablesLayout.createSequentialGroup()
                                    .addComponent(lblLabelRowsScanned, javax.swing.GroupLayout.DEFAULT_SIZE, 216, Short.MAX_VALUE)
                                    .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)))
                            .addGroup(pnlTablesLayout.createSequentialGroup()
                                .addComponent(lblLabelScanTime, javax.swing.GroupLayout.PREFERRED_SIZE, 81, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addGap(132, 132, 132)))
                        .addGroup(pnlTablesLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(pnlTablesLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                                .addComponent(lblTaintedRows, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(lblTaintedTables, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(lblRowsScanned, javax.swing.GroupLayout.DEFAULT_SIZE, 53, Short.MAX_VALUE)
                                .addComponent(lblTablesScanned, javax.swing.GroupLayout.PREFERRED_SIZE, 61, javax.swing.GroupLayout.PREFERRED_SIZE))
                            .addGroup(pnlTablesLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false)
                                .addComponent(lblTaintedTuples, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(lblScanTime, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, 57, Short.MAX_VALUE)))
                        .addGap(71, 71, 71))
                    .addGroup(pnlTablesLayout.createSequentialGroup()
                        .addComponent(lstTable, javax.swing.GroupLayout.PREFERRED_SIZE, 278, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addContainerGap())))
        );
        pnlTablesLayout.setVerticalGroup(
            pnlTablesLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(pnlTablesLayout.createSequentialGroup()
                .addContainerGap()
                .addGroup(pnlTablesLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lbLabellTablesScanned)
                    .addComponent(lblTablesScanned))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(pnlTablesLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblLabelNumTablesTainted)
                    .addComponent(lblTaintedTables))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(pnlTablesLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblRowsScanned)
                    .addComponent(lblLabelRowsScanned))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(pnlTablesLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblLabelTaintedRows)
                    .addComponent(lblTaintedRows, javax.swing.GroupLayout.PREFERRED_SIZE, 14, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(pnlTablesLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblLabelTaintedTuples)
                    .addComponent(lblTaintedTuples))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(pnlTablesLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblLabelScanTime)
                    .addComponent(lblScanTime))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 63, Short.MAX_VALUE)
                .addComponent(lblLabelTaintedTables)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(lstTable, javax.swing.GroupLayout.PREFERRED_SIZE, 235, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap())
        );

        pnlRows.setBorder(javax.swing.BorderFactory.createTitledBorder("Rows"));

        pnlTableRows.setBorder(null);
        pnlTableRows.setOrientation(javax.swing.JSplitPane.VERTICAL_SPLIT);
        pnlTableRows.setAutoscrolls(true);
        pnlTableRows.setMinimumSize(new java.awt.Dimension(10, 5));

        tblRows.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {},
                {},
                {},
                {}
            },
            new String [] {

            }
        ));
        pnRowsHolder.setViewportView(tblRows);

        pnlTableRows.setLeftComponent(pnRowsHolder);

        pnlIssueDetails.setBorder(javax.swing.BorderFactory.createTitledBorder("Details"));

        lblOriginalTupleValue.setText("Original Tuple Value:");

        lblCleanedTupleValue.setText("Cleaned Tuple Value:");

        txtOriginalValue.setColumns(20);
        txtOriginalValue.setEditable(false);
        txtOriginalValue.setRows(5);
        scrollOriginalValue.setViewportView(txtOriginalValue);

        txtCleanedValue.setColumns(20);
        txtCleanedValue.setRows(5);
        scrollCleanedValue.setViewportView(txtCleanedValue);

        btnFix.setLabel("Try Fix");
        btnFix.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnFixActionPerformed(evt);
            }
        });

        btnSkip.setText("Skip");
        btnSkip.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnSkipActionPerformed(evt);
            }
        });

        btnMarkClean.setText("Ignore");
        btnMarkClean.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnMarkCleanActionPerformed(evt);
            }
        });

        btnExplain.setText("Why was this flagged?");
        btnExplain.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnExplainActionPerformed(evt);
            }
        });

        btnGenerateReport.setText("Generate Report...");
        btnGenerateReport.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnGenerateReportActionPerformed(evt);
            }
        });

        btnManageColumns.setText("Manage Columns...");
        btnManageColumns.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnManageColumnsActionPerformed(evt);
            }
        });

        lblStatus.setText(" ");

        javax.swing.GroupLayout pnlIssueDetailsLayout = new javax.swing.GroupLayout(pnlIssueDetails);
        pnlIssueDetails.setLayout(pnlIssueDetailsLayout);
        pnlIssueDetailsLayout.setHorizontalGroup(
            pnlIssueDetailsLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(pnlIssueDetailsLayout.createSequentialGroup()
                .addContainerGap()
                .addGroup(pnlIssueDetailsLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(lblOriginalTupleValue, javax.swing.GroupLayout.PREFERRED_SIZE, 148, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(scrollOriginalValue, javax.swing.GroupLayout.DEFAULT_SIZE, 743, Short.MAX_VALUE)
                    .addComponent(lblCleanedTupleValue, javax.swing.GroupLayout.PREFERRED_SIZE, 123, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(scrollCleanedValue, javax.swing.GroupLayout.DEFAULT_SIZE, 743, Short.MAX_VALUE)
                    .addGroup(pnlIssueDetailsLayout.createSequentialGroup()
                        .addGap(10, 10, 10)
                        .addGroup(pnlIssueDetailsLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false)
                            .addComponent(lblStatus, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addGroup(javax.swing.GroupLayout.Alignment.LEADING, pnlIssueDetailsLayout.createSequentialGroup()
                                .addComponent(btnFix, javax.swing.GroupLayout.PREFERRED_SIZE, 80, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addComponent(btnMarkClean)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addComponent(btnSkip)
                                .addGap(16, 16, 16)
                                .addComponent(btnGenerateReport)))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(btnManageColumns)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 112, Short.MAX_VALUE)
                        .addComponent(btnExplain)))
                .addContainerGap())
        );
        pnlIssueDetailsLayout.setVerticalGroup(
            pnlIssueDetailsLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(pnlIssueDetailsLayout.createSequentialGroup()
                .addContainerGap()
                .addComponent(lblOriginalTupleValue)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(scrollOriginalValue, javax.swing.GroupLayout.PREFERRED_SIZE, 50, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(lblCleanedTupleValue)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(scrollCleanedValue, javax.swing.GroupLayout.PREFERRED_SIZE, 53, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(pnlIssueDetailsLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(btnFix)
                    .addComponent(btnExplain)
                    .addComponent(btnGenerateReport)
                    .addComponent(btnManageColumns)
                    .addComponent(btnSkip)
                    .addComponent(btnMarkClean))
                .addGap(18, 18, 18)
                .addComponent(lblStatus)
                .addContainerGap(147, Short.MAX_VALUE))
        );

        pnlTableRows.setRightComponent(pnlIssueDetails);

        javax.swing.GroupLayout pnlRowsLayout = new javax.swing.GroupLayout(pnlRows);
        pnlRows.setLayout(pnlRowsLayout);
        pnlRowsLayout.setHorizontalGroup(
            pnlRowsLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(pnlRowsLayout.createSequentialGroup()
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addComponent(pnlTableRows, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
        );
        pnlRowsLayout.setVerticalGroup(
            pnlRowsLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, pnlRowsLayout.createSequentialGroup()
                .addContainerGap()
                .addComponent(pnlTableRows, javax.swing.GroupLayout.DEFAULT_SIZE, 427, Short.MAX_VALUE)
                .addContainerGap())
        );

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addComponent(pnlTables, javax.swing.GroupLayout.PREFERRED_SIZE, 311, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(pnlRows, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(pnlTables, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
            .addGroup(layout.createSequentialGroup()
                .addComponent(pnlRows, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addGap(11, 11, 11))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void btnManageColumnsActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnManageColumnsActionPerformed

        if (this.lstTable.getSelectedIndex() != -1) {
            dlgColumns.setVisible(true);
            updateTable();


            //use the results of the dialog to hide / show columns
            tblRows.doLayout();
            this.repaint();
        }

    }//GEN-LAST:event_btnManageColumnsActionPerformed

    private void btnGenerateReportActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnGenerateReportActionPerformed

        JFileChooser fc = new JFileChooser("Write Report To...");
        fc.setSelectedFile(new File("scrubbr_" + lstTable.getSelectedItem() + "_" + (new Date().getTime()) + ".csv"));
        int returnVal = fc.showSaveDialog(this);
        fc.setFileFilter(new CSVFilter());

        if (returnVal == JFileChooser.APPROVE_OPTION) {

            File file = fc.getSelectedFile();

            if (file.exists() && !file.canWrite()) {
                errorMessage("Can't write to file selected");
                return;
            }

            try {


                PrintWriter pw = new PrintWriter(new FileWriter(file));
                pw.println("# Scrubbr scan results of the '" + lstTable.getSelectedItem() + "' table");

                DBTableModel model = (DBTableModel) tblRows.getModel();
                Table t = model.getTable();

                System.out.print("# ");

                for (int i = 0; i < model.getColumnCount(); i++) {
                    System.out.print(model.getColumnName(i));
                    if (i != model.getColumnCount() - 1) {
                        System.out.println(", ");
                    }
                }

                System.out.println();

                //first, print column headers
                pw.print("SCRUBBR STATUS,");
                for (int j = 0; j < model.getColumnCount(); j++) {
                    Tuple tuple = (Tuple) model.getValueAt(0, j);

                    if (tuple == null) {
                        break;
                    }
                    pw.print(escapeForCSV(tuple.getName()));

                    if (j != (model.getColumnCount() - 1)) {
                        pw.print(",");
                    } else {
                        pw.print("\n");
                    }
                }


                for (int i = 0; i < model.getRowCount(); i++) {


                    for (int j = 0; j < model.getColumnCount(); j++) {
                        Tuple tuple = (Tuple) model.getValueAt(i, j);

                        if (tuple == null) {
                            break;
                        }

                        if (tuple.isTainted()) {
                            System.out.print("<tainted>");
                        }

                        System.out.print(escapeForCSV(tuple.getValue()));

                        if (tuple.isTainted()) {
                            System.out.print("</tainted><cleaned>" + escapeForCSV(tuple.getCleanedValue()) + "</cleaned>");
                        }

                        if (j != model.getColumnCount()) {
                            System.out.print(", ");
                        }
                    }


                    //create cs file

                    pw.print("ORIGINAL:,");
                    for (int j = 0; j < model.getColumnCount(); j++) {
                        Tuple tuple = (Tuple) model.getValueAt(i, j);

                        if (tuple == null) {
                            break;
                        }
                        pw.print(escapeForCSV(tuple.getValue()));

                        if (j != (model.getColumnCount() - 1)) {
                            pw.print(",");
                        } else {
                            pw.print("\n");
                        }
                    }

                    pw.print("CLEANED:,");
                    for (int j = 0; j < model.getColumnCount(); j++) {
                        Tuple tuple = (Tuple) model.getValueAt(i, j);

                        if (tuple == null) {
                            break;
                        }

                        String repair = tuple.getCleanedValue();

                        if (repair == null) {
                            repair = "--N/A--";
                        }

                        pw.print(escapeForCSV(repair));

                        if (j != (model.getColumnCount() - 1)) {
                            pw.print(",");
                        } else {
                            pw.print("\n");
                        }
                    }

                    pw.print(",");
                    for (int j = 0; j < model.getColumnCount(); j++) {
                        Tuple tuple = (Tuple) model.getValueAt(i, j);

                        if (tuple == null) {
                            break;
                        }
                        pw.print("");

                        if (j != (model.getColumnCount() - 1)) {
                            pw.print(",");
                        } else {
                            pw.print("\n");
                        }
                    }

                    System.out.println();
                }

                pw.close();

            } catch (IOException ex) {
                ex.printStackTrace();
            }

        }

    }//GEN-LAST:event_btnGenerateReportActionPerformed

    private String escapeForCSV(String s) {

        if (s == null) {
            return s;
        }

        s = s.trim();

        /*
         *For CSV:
        -if it has commas, surround the cell with quotes
        -if this cell has quotes, escape them with another quout
        -leave no spaces between cells (cell,cell NOT cell, cell)
         */

        boolean wrapInQuotes = false;

        if (s.indexOf(",") > -1) {

            if (s.indexOf("\"") > -1) {
                s = s.replace("\"", "\"\"");
            }

            wrapInQuotes = true;
        }

        if (s.indexOf("\n") > -1) {
            wrapInQuotes = true;
        }

        if (wrapInQuotes) {
            s = "\"" + s + "\"";
        }

        return s;
    }

    private void btnExplainActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnExplainActionPerformed

        DBTableModel db = (DBTableModel) tblRows.getModel();

        int x = tblRows.getSelectedRow();
        int y = tblRows.getSelectedColumn();

        Tuple t = (Tuple) db.getValueAt(x, y);

        String tableName = db.getTable().getName();
        String columnName = tblRows.getColumnName(y);

        String s = tableName + " > " + columnName + "\r\n\r\n";

        Iterator it = t.getErrors().iterator();

        while (it.hasNext()) {
            String err = (String) it.next();
            s += GeneralUtil.htmlDecode(err) + "\r\n";
        }

        infoMessage(s);

    }//GEN-LAST:event_btnExplainActionPerformed

    private void formWindowClosing(java.awt.event.WindowEvent evt) {//GEN-FIRST:event_formWindowClosing

        if (confirm("Are you sure you want to close? This means you will have to re-scan to get this data.")) {
            this.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
        } else {
            this.setDefaultCloseOperation(JFrame.DO_NOTHING_ON_CLOSE);
        }

    }//GEN-LAST:event_formWindowClosing

    private boolean confirm(String s) {
        int i = JOptionPane.showConfirmDialog(this, s, "Scrubbr", JOptionPane.YES_NO_OPTION);

        return i == JOptionPane.YES_OPTION;
    }

    private void moveToNextIssue(int x, int y) {

        DBTableModel db = (DBTableModel) tblRows.getModel();

        for (int a = y; a < tblRows.getRowCount(); a++) {

            int b = 0;

            if (a == y) {
                b = x + 1;
            }

            for (; b < tblRows.getColumnCount(); b++) {

                Tuple tuple = (Tuple) db.getValueAt(a, b);

                if (tuple.isTainted()) {
                    //infoMessage("moving to (" + a + "," + b + ")");
                    tblRows.changeSelection(a, b, false, false);
                    btnFix.setEnabled(true);
                    btnFix.requestFocusInWindow();
                    return;
                }

            }
        }


        Object[] options = {"Resume From Top",
            "Stop"};

        int n = JOptionPane.showOptionDialog(this, "The search has reached the end of the results.  Would you like to resume from the begining?", "",
                JOptionPane.YES_NO_OPTION,
                JOptionPane.QUESTION_MESSAGE,
                null,
                options,
                options[0]);

        if (n == JOptionPane.YES_OPTION) {
            moveToNextIssue(0, 0);
        } else {
        }


    }

    private void lstTableItemStateChanged(java.awt.event.ItemEvent evt) {//GEN-FIRST:event_lstTableItemStateChanged


        //System.out.println("list selection changed");

        Table t = rpt.getScannedTables().get(Integer.parseInt(evt.getItem().toString()));

        loadRows(t);

    }//GEN-LAST:event_lstTableItemStateChanged

    private void btnFixActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnFixActionPerformed

        lblStatus.setText(" ");
        // update issue in db of current cell
        int x = tblRows.getSelectedRow();
        int y = tblRows.getSelectedColumn();
        int numAffectedRows = 0;

        if (x < 0 || y < 0) {
            return;
        }

        DBTableModel db = (DBTableModel) tblRows.getModel();

        Tuple t = (Tuple) db.getValueAt(x, y);

        if (!t.isTainted()) {
            return;
        }

        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "select * from " + dbObjectWrap(lstTable.getSelectedItem()) +
                " where " + dbObjectWrap(t.getName()) + "=?";

        try {

            c = DatabaseUtil.getConnection();
            ps = c.prepareStatement(sql);
            ps.setString(1, t.getValue());

            try {
                rs = ps.executeQuery();
            } catch (SQLException e) {

                if (e.getMessage().contains("nvarchar") && e.getMessage().contains("incompatible")) {
                    sql = "select * from " +
                            dbObjectWrap(lstTable.getSelectedItem()) +
                            " where CAST(" + dbObjectWrap(t.getName()) + " as nvarchar(max)) = ?";
                    ps = c.prepareStatement(sql);
                    ps.setString(1, t.getValue());
                    rs = ps.executeQuery();

                } else {
                    throw e;
                }

            }

            while (rs.next()) {
                numAffectedRows++;
            }

        } catch (Exception e) {

            errorMessage("Error calculating affected rows: " + e.getMessage());
            return;

        } finally {
            DatabaseUtil.safeClose(rs);
            DatabaseUtil.safeClose(ps);
            DatabaseUtil.safeClose(c);
        }

        if (!confirm("This will update every row in the '" +
                lstTable.getSelectedItem() + "' table with the following" +
                " value:\n\n" + trim(t.getValue(), 80) + "\n\n" +
                "... with the value in the \"Cleaned value\" box. Are " +
                "you sure you want to do this?\n" +
                "This will affect " + numAffectedRows + " rows.")) {

            return;
        }
        /*
         * Build the query
         */

        sql = "update " + dbObjectWrap(lstTable.getSelectedItem()) + " set " + dbObjectWrap(t.getName()) + "=? where " + dbObjectWrap(t.getName()) + "=?";

        /*
         * Add the parameters
         */

        try {

            c = DatabaseUtil.getConnection();
            ps = c.prepareStatement(sql);

            ps.setString(1, txtCleanedValue.getText());
            ps.setString(2, t.getValue());

            System.out.println("foo: " + sql);

            try {

                numAffectedRows = ps.executeUpdate();

            } catch (SQLException e) {

                System.out.println("Got initial exception: "+e.getMessage());
                if (e.getMessage().contains("nvarchar") && e.getMessage().contains("incompatible")) {

                    String fieldType = "ntext";
                    if ( ! e.getMessage().contains("ntext") && e.getMessage().contains(" text ")) {
                        fieldType = "text";
                    }

                    sql = "update " + dbObjectWrap(lstTable.getSelectedItem()) + " set " + dbObjectWrap(t.getName()) + "=CONVERT(" + fieldType + ", N'" + escapeSingleQuotes(txtCleanedValue.getText()) + "') where CAST(" + dbObjectWrap(t.getName()) + " as nvarchar(max))=?";
                    System.out.println("Trying new query: "+sql);
                    ps = c.prepareStatement(sql);

                    //ps.setString(1, txtCleanedValue.getText());
                    ps.setString(1, t.getValue());
                    
                    numAffectedRows = ps.executeUpdate();

                } else {
                    throw e;
                }


            }

            lblStatus.setText("Cleaned " + numAffectedRows + " rows!");
            String originalDirtyValue = t.getValue();

            System.out.println("looking for " + t.getValue());

            /* mark all tainted cell with that value as cleaned */
            for (int i = 0; i < tblRows.getRowCount(); i++) {

                Tuple tmp = (Tuple) db.getValueAt(i, y);

                if (tmp.getValue() != null) {

                    if (tmp.getValue().equals(originalDirtyValue)) {
                        tmp.setTainted(false);
                        tmp.setValue(t.getCleanedValue());
                    }

                }

            }

            updateTable();

        } catch (Exception e) {
            errorMessage("Could not fix entry: " + e.getMessage());
            e.printStackTrace();
            return;
        }

        // iterate through columns, and if need be, rows in order to find the next problem cell
        moveToNextIssue(x, y);

    }//GEN-LAST:event_btnFixActionPerformed

    private void btnMarkCleanActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnMarkCleanActionPerformed

        lblStatus.setText(" ");

        // update issue in db of current cell
        int x = tblRows.getSelectedRow();
        int y = tblRows.getSelectedColumn();

        if (x < 0 || y < 0) {
            return;
        }

        int i = confirmMessage("Are you sure you want to mark this data as clean? Many attacks can be encoded and obfuscated in order to make them look innocent or normal.\r\n\r\nNote that this mark is not permanent - another scan will cite the same data.");

        if (i == JOptionPane.YES_OPTION) {

            DBTableModel db = (DBTableModel) tblRows.getModel();

            Tuple t = (Tuple) db.getValueAt(x, y);

            if (t.isTainted()) {
                t.setTainted(false);
            }

            updateTable();

        }

    }//GEN-LAST:event_btnMarkCleanActionPerformed

    private void btnSkipActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnSkipActionPerformed

        lblStatus.setText(" ");

        // update issue in db of current cell
        int x = tblRows.getSelectedColumn();
        int y = tblRows.getSelectedRow();

        if (x >= 0 && y >= 0) {
            moveToNextIssue(x, y);
        }
    }//GEN-LAST:event_btnSkipActionPerformed

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        /*
        java.awt.EventQueue.invokeLater(new Runnable() {
        public void run() {
        new ReportFrame(new Report()).setVisible(true);
        }
        });
         */
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton btnExplain;
    private javax.swing.JButton btnFix;
    private javax.swing.JButton btnGenerateReport;
    private javax.swing.JButton btnManageColumns;
    private javax.swing.JButton btnMarkClean;
    private javax.swing.JButton btnSkip;
    private javax.swing.JLabel lbLabellTablesScanned;
    private javax.swing.JLabel lblCleanedTupleValue;
    private javax.swing.JLabel lblLabelNumTablesTainted;
    private javax.swing.JLabel lblLabelRowsScanned;
    private javax.swing.JLabel lblLabelScanTime;
    private javax.swing.JLabel lblLabelTaintedRows;
    private javax.swing.JLabel lblLabelTaintedTables;
    private javax.swing.JLabel lblLabelTaintedTuples;
    private javax.swing.JLabel lblOriginalTupleValue;
    private javax.swing.JLabel lblRowsScanned;
    private javax.swing.JLabel lblScanTime;
    private javax.swing.JLabel lblStatus;
    private javax.swing.JLabel lblTablesScanned;
    private javax.swing.JLabel lblTaintedRows;
    private javax.swing.JLabel lblTaintedTables;
    private javax.swing.JLabel lblTaintedTuples;
    private java.awt.List lstTable;
    private javax.swing.JScrollPane pnRowsHolder;
    private javax.swing.JPanel pnlIssueDetails;
    private javax.swing.JPanel pnlRows;
    private javax.swing.JSplitPane pnlTableRows;
    private javax.swing.JPanel pnlTables;
    private javax.swing.JScrollPane scrollCleanedValue;
    private javax.swing.JScrollPane scrollOriginalValue;
    private javax.swing.JTable tblRows;
    private javax.swing.JTextArea txtCleanedValue;
    private javax.swing.JTextArea txtOriginalValue;
    // End of variables declaration//GEN-END:variables

    private void errorMessage(String msg) {
        JOptionPane.showMessageDialog(this, msg, "Aspect XSS DB Scrubbr", JOptionPane.ERROR_MESSAGE);
    }

    private void infoMessage(String msg) {
        JOptionPane.showMessageDialog(this, msg, "Aspect XSS DB Scrubbr", JOptionPane.INFORMATION_MESSAGE);
    }

    private int confirmMessage(String msg) {
        return JOptionPane.showConfirmDialog(this, msg, "Aspect XSS DB Scrubbr", JOptionPane.YES_NO_OPTION);
    }

    private void updateTable() {

        /* get the row the tainted value was in - if there's no more issues in the row, then throw it out */
        DBTableModel db = (DBTableModel) tblRows.getModel();
        int currentRow = tblRows.getSelectedRow();
        TaintedRow tr = (TaintedRow) db.getTable().getTaintedRows().get(currentRow);

        boolean rowStillTainted = false;

        for (int i = 0; i < tr.getAllTuples().size(); i++) {
            if (tr.getAllTuples().get(i).isTainted()) {
                rowStillTainted = true;
            }
        }

        if (!rowStillTainted) {
            db.getTable().getTaintedRows().remove(currentRow);
        }

        tblRows.updateUI();

        btnFix.setEnabled(false);
        btnMarkClean.setEnabled(false);
        btnSkip.setEnabled(true);
        txtOriginalValue.setText("");
        txtCleanedValue.setText("");
        txtOriginalValue.setEnabled(false);
        txtCleanedValue.setEnabled(false);
        txtOriginalValue.setBackground(Color.GRAY);
        txtCleanedValue.setBackground(Color.GRAY);

        if (db.getTable().getTaintedRows().size() == 0) {

            infoMessage("No issues remaining in current table '" + db.getTable().getName() + "'.");
            btnSkip.setEnabled(false);
        }

    }

    private String trim(String s, int l) {

        if (s == null) {
            return null;
        }

        if (s.length() > l) {
            return s.substring(0, l);
        }

        return s;
    }

    private String escapeSingleQuotes(String s) {
        if ( s == null )
                return null;

        return s.replaceAll("'", "''");
    }
}
